package JDBC;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import SwingModel.CmsAssignment;
import SwingModel.CmsStudent;

public class InstructorAssignmentActivities {

	public static void postAssignment(String courseID, CmsAssignment asgm){
		try{
		
			//Database Connection and Retrieve data
			
			System.out.println("Inside post assignment");
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement(
					"insert into Assignments (CourseID, AssigTitle, AssigDescr, PercWeight, CreatedDTM, AssigAvailDTM, AssigDueDTM, AssigExpDTM)"
					+ "values (?, ?, ?, ?, ?, ?, ?, ?);");
			
			pstmt.setString(1,courseID);
			
			pstmt.setString(2,asgm.getAsgmTitle());

			pstmt.setString(3,asgm.getAsgmDescription());


			pstmt.setDouble(4, asgm.getPercentWeight());
			
			SimpleDateFormat sdf = new SimpleDateFormat("YYYY:MM:dd HH:mm:ss");			
			pstmt.setString(5, sdf.format(asgm.getCreatedDate()));
			
			pstmt.setString(6, sdf.format(asgm.getAvaiDate()));
			
			pstmt.setString(7, sdf.format(asgm.getDueDate())); 
			
			pstmt.setString(8, sdf.format(asgm.getExpDate())); 
												          		
			pstmt.execute();
		
		    pstmt = dCon.connection.prepareStatement(
					"select * from Assignments where CourseId=? and CreatedDTM=?");
		    
			pstmt.setString(1,courseID);			
			pstmt.setString(2, sdf.format(asgm.getCreatedDate()));
			ResultSet resultSet = pstmt.executeQuery();
			int asgmID = 0;
			
			while(resultSet.next())
			{
				asgmID = resultSet.getInt("AssigId");
				System.out.println(+asgmID);
			}
			
		   		
			List<File> asgmFile = asgm.getListOfFile();
			
			for(File file : asgmFile){

				pstmt = dCon.connection.prepareStatement("insert into Assig_Files ( AssigId, AssigFilePath)" + "values (?, ?);");
				
				pstmt.setInt(1,asgmID);
				pstmt.setString(2, file.getPath());
				pstmt.execute();
				System.out.println("File is added to database");
				System.out.println(asgm.getAsgmTitle() + "File is added to the DB");
				
			}
			
			//create assignment entries for students
			List<CmsStudent> students = PeopleActivities.getListOfStudent(courseID);
			System.out.println("LIST OF STUDENTS------------");
			for(CmsStudent student: students){
				System.out.println(student.getFirstName() + " " + student.getLastName());
				pstmt = dCon.connection.prepareStatement("insert into User_Assig ( AssigId, Username, Grade,IsPublisher)" + "values (?, ?, ?, ?);");
				pstmt.setInt(1, asgmID);
				pstmt.setString(2, student.getUsername());
				pstmt.setDouble(3, 0.0);
				pstmt.setInt(4, 0);
				pstmt.execute();
			}
			
			dCon.close();				
		} catch(Exception e){
			e.printStackTrace();
			//System.out.println("\nUnsuccessful query while inserting a new assignment into the Assignments table!\n");
			//System.out.println("Error Message: " + e.toString());
		}
	}
	
	
	//To view list of posted assignments
	public static List<CmsAssignment> getListOfAssignments(String courseID){
		List<CmsAssignment> listOfAssignments = new ArrayList();
		
		 try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			System.out.println("Retrieve assignment for this courseID - " + courseID);
			pstmt = dCon.connection.prepareStatement("select * from dbcms.Assignments where courseID=? order by CreatedDTM desc");
			pstmt.setString(1,courseID);
			ResultSet resultSet = pstmt.executeQuery();
			
			while (resultSet.next()){
				System.out.println("InstructorAssignmentActivities.getListOfAssignment -Inside ResultSet");
				
				int asgmId= resultSet.getInt("AssigId"); 
				
				System.out.println("Retrieve thsi asgmID - " + asgmId);
				pstmt = dCon.connection.prepareStatement("select * from dbcms.Assig_Files where AssigId=?");
				pstmt.setInt(1, asgmId);
				ResultSet fileResultSet = pstmt.executeQuery();// different result set to store the Assig_Files data
				
				List<File> asgmFile = new ArrayList(); 
				
				while(fileResultSet.next()){
					System.out.println("File path - " + fileResultSet.getString("AssigFilePath"));
					asgmFile.add(new File(fileResultSet.getString("AssigFilePath")));
				}
				
				//add assignment to the list 
				CmsAssignment asgm =new CmsAssignment(asgmId, resultSet.getString("AssigTitle"), resultSet.getString("AssigDescr"), asgmFile, 
						resultSet.getDouble("PercWeight"),resultSet.getTimestamp("CreatedDTM"), resultSet.getTimestamp("AssigAvailDTM"), resultSet.getTimestamp("AssigDueDTM"), 
						resultSet.getTimestamp("AssigExpDTM"));
				
				System.out.println("RETRIEVED ASSIGNMENT:\n" + asgm.toString() + "\n-----------------------\n\n");
				listOfAssignments.add(asgm);
				
			}
			
			
		} catch(Exception e){}
		
		return listOfAssignments;
	}

	
	//deleted selected Assignment
	public static void deleteAssignment(int asgmID){
		System.out.println("Delete assignmentID: " + asgmID);
		
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			
			pstmt = dCon.connection.prepareStatement(
					"SET foreign_key_checks = 0;");
			pstmt.execute();
			
			pstmt = dCon.connection.prepareStatement(
					"delete from Assig_Files where AssigId = ?");
			pstmt.setInt(1, asgmID);
			
			pstmt.execute();
			
			pstmt = dCon.connection.prepareStatement(
					"delete from assig_submission where AssigId = ?");
			pstmt.setInt(1, asgmID);
			
			pstmt.execute();
			
			
			
			pstmt = dCon.connection.prepareStatement(
					"delete from user_assig where AssigId = ?;");
			pstmt.setInt(1, asgmID);
			
			pstmt.execute();
			
			
			
			
			pstmt = dCon.connection.prepareStatement(
					"delete from Assignments where AssigId = ?;");
			pstmt.setInt(1,asgmID );
			
			pstmt.execute();
			
			pstmt = dCon.connection.prepareStatement(
					"SET foreign_key_checks = 1;");
			
			pstmt.execute();

		} catch(Exception e){
			e.printStackTrace();
		}
	} 
	
	
	public static void updateGrade(int asgmID, String username, double grade)
	{
		System.out.println("Inside update grade!!");
		try
		{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement(
					"update dbcms.User_Assig SET  Grade = ? WHERE assigId = ? AND username = ? ;");
			
			pstmt.setDouble(1,grade);
			pstmt.setInt(2,asgmID);
			pstmt.setString(3, username);
				
			pstmt.execute();
		} 
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
	}


	public static void editAssignment(String courseID, CmsAssignment edittedAsgm) {
		int asgmID = edittedAsgm.getAsgmID();
		try{
			//Database Connection and Retrieve data
			System.out.println("Inside Edit Assignment");
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement(
					"delete from dbcms.Assig_Files where AssigId = ?");
			pstmt.setInt(1, asgmID);
			
			pstmt.execute();
			
			pstmt = dCon.connection.prepareStatement(
					"UPDATE dbcms.Assignments SET  CourseId=?, AssigTitle=?, AssigDescr=?, PercWeight=?, CreatedDTM=?, AssigAvailDTM=?, AssigDueDTM=?, AssigExpDTM=? WHERE AssigId = ?");
			
			pstmt.setString(1,courseID);
			
			pstmt.setString(2,edittedAsgm.getAsgmTitle());

			pstmt.setString(3,edittedAsgm.getAsgmDescription());

			pstmt.setDouble(4, edittedAsgm.getPercentWeight());
			
			SimpleDateFormat sdf = new SimpleDateFormat("YYYY:MM:dd HH:mm:ss");
			pstmt.setString(5, sdf.format(edittedAsgm.getCreatedDate()));
			
			pstmt.setString(6, sdf.format(edittedAsgm.getAvaiDate()));
			
			pstmt.setString(7, sdf.format(edittedAsgm.getDueDate())); 
			
			pstmt.setString(8, sdf.format(edittedAsgm.getExpDate()));
			pstmt.setInt(9, edittedAsgm.getAsgmID());
			
        	pstmt.execute();			
			System.out.println("Updated Assignement info");
			
			 pstmt = dCon.connection.prepareStatement(
			"select * from dbcms.Assignments where CourseId=? and CreatedDTM=?");
    
	         pstmt.setString(1,courseID);			
	         pstmt.setString(2, sdf.format(edittedAsgm.getCreatedDate()));
	         ResultSet resultSet = pstmt.executeQuery();
	        	
	         while(resultSet.next())
	        {
		      asgmID = resultSet.getInt("AssigId");
		      System.out.println(+asgmID);
	        }
			
							
					
            List<File> asgmFile = edittedAsgm.getListOfFile();
			
			for(File file : asgmFile){

				pstmt = dCon.connection.prepareStatement("insert into Assig_Files ( AssigId, AssigFilePath)" + "values (?, ?);");
				
				pstmt.setInt(1,asgmID);
				pstmt.setString(2, file.getPath());
				pstmt.execute();
				System.out.println("File is added to database");
				System.out.println(edittedAsgm.getAsgmTitle() + "File is added to the DB");
				
			}
			
		System.out.println("Done execution");
		dCon.close();

		} catch(Exception e){
			e.printStackTrace();
			System.out.println("\nUnsuccessful query while inserting a new assignment into the Assignments table!\n");
			System.out.println("Error Message: " + e.toString());
			
			
		}

	}
		
	}

